const sqlite3 = require("sqlite3").verbose();
const config = require("../../common/config");
const util = require("../../common/util");

class UserDB {
  /**
   * @constructor
   * @private
   */
  constructor() {
    this.dbFile = config.dbFile;
    this.instance = null;
    this.db = null;
    this.register = this.add;
  }

  /**
   * 创建UserDB对象
   * @returns {UserDB} UserDB实例
   */
  static getInstance() {
    if (!this.instance) {
      this.instance = new UserDB();
    }
    return this.instance;
  }

  // connect() 和 close() 方法保持原样

  /**
   * 获取指定ID的用户信息
   * @param {Number} userId 用户ID
   * @returns {Promise}
   */
  findById(userId) {
    return new Promise((resolve, reject) => {
      const sql = "SELECT * FROM users WHERE user_id = ?";
      this.db.get(sql, [userId], (err, result) => {
        err ? reject(err) : resolve(result);
      });
    });
  }

  /**
   * 获取用户信息列表
   * @param {Number} limit 数量 为查询总数的限制，默认为-1，表示不限制
   * @param {Number} offset 开始 为查询的起始位置，
   * @returns  {Promise}
   * 测试通过
   */
  findAll(limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
      let sql = "";
      let params = [];
      if (limit === -1) {
        sql = "SELECT * FROM users ORDER BY user_id";
      } else if (offset === -1) {
        sql = "SELECT * FROM users ORDER BY user_id LIMIT ?";
        params[0] = limit;
      } else {
        sql = "SELECT * FROM users ORDER BY user_id LIMIT ? OFFSET ?";
        params[0] = limit;
        params[1] = offset;
      }
      this.db.all(sql, params, (err, result) => {
        if (err) {
          reject(err);
        } else {
          util.log(`这是结果${result}`);
          resolve(result);
        }
      });
    });
  }

  /**
   * 创建新用户
   * @param {Object} user 用户数据
   * @returns {Promise}
   */
  add(user) {
    return new Promise((resolve, reject) => {
      const sql = `
          INSERT INTO users (
            username, password, student_id, real_name, email, phone,
            avatar_url, role, status, last_login_time, last_login_ip,
            created_ip, created_at, updated_at, nickname
          ) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
          )`;

      const params = [
        user.username,
        user.password,
        user.student_id || null,
        user.real_name || null,
        user.email || null,
        user.phone || null,
        user.avatar_url || null,
        user.role || 1,
        user.status || 1,
        user.last_login_time || null,
        user.last_login_ip || null,
        user.created_ip || null,
        user.created_at || null,
        user.updated_at || null,
        user.nickname,
      ];

      this.db.run(sql, params, function (err) {
        if (err) return reject(err);
        util.log(`Created user ID: ${this.lastID}`);
        resolve(this.lastID);
      });
    });
  }
  /**
   * 更新用户信息
   * @param {Object} user 用户数据
   * @returns {Promise}
   */
  update(user) {
    return new Promise((resolve, reject) => {
      const sql = `
          UPDATE users SET
          nickname = ?, student_id = ?,
          real_name = ?, email = ?, phone = ?
          WHERE user_id = ?`;
      let params = [
        user.nickname,
        user.student_id,
        user.real_name,
        user.email,
        user.phone,
        user.user_id,
      ];

      this.db.run(sql, params, function (err) {
        err ? reject(err) : resolve(this.changes);
      });
    });
  }

  avatar(user_id, url) {
    return new Promise((resolve, reject) => {
      let sql = `UPDATE users SET avatar_url = ? WHERE user_id = ?`;
      let params = [url, user_id];
      util.log("&&&&&&&&&&&&&&&&&&&&&&&&&&&&");
      util.log(params);
      util.log("&&&&&&&&&&&&&&&&&&&&&&&&&&&&");
      this.db.run(sql, params, function (err) {
        err ? reject(err) : resolve(this.changes);
      });
    });
  }

  /**
   * 删除用户
   * @param {Number} userId 用户ID
   * @returns {Promise}
   */
  remove(userId) {
    return new Promise((resolve, reject) => {
      const sql = "DELETE FROM users WHERE user_id = ?";
      this.db.run(sql, [userId], function (err) {
        err ? reject(err) : resolve(this.changes);
      });
    });
  }

  // 以下是扩展方法

  /**
   * 通过用户名查找用户
   * @param {String} username 用户名
   * @returns {Promise}
   */
  findByUsername(username) {
    return new Promise((resolve, reject) => {
      const sql = "SELECT * FROM users WHERE username = ?";
      this.db.get(sql, [username], (err, result) => {
        err ? reject(err) : resolve(result);
      });
    });
  }

  /**
   * 更新登录信息
   * @param {Number} userId 用户ID
   * @param {String} ip 登录IP
   * @returns {Promise}
   */
  updateLoginInfo(userId, ip) {
    return new Promise((resolve, reject) => {
      const sql = `
          UPDATE users SET
            last_login_time = datetime('now'),
            last_login_ip = ?,
            login_count = login_count + 1
          WHERE user_id = ?`;

      this.db.run(sql, [ip, userId], function (err) {
        err ? reject(err) : resolve(this.changes);
      });
    });
  }

  // 核心：异步连接方法（Promise 化）
  async connect() {
    if (this.isConnected) return; // 避免重复连接
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbFile, (err) => {
        if (err) {
          util.err("数据库连接失败", err);
          return reject(err);
        } else {
          this.isConnected = true;
          util.log("数据库连接成功");
          resolve();
        }
      });
      // 连接错误监听（持续重连逻辑）
      this.db.on("error", (err) => {
        util.err("数据库连接断开", err);
        this.isConnected = false;
        // 可选：5秒后自动重连
        setTimeout(() => this.connect(), 5000);
      });
    });
  }

  // 安全关闭连接（可选）
  async close() {
    if (!this.isConnected) return;
    return new Promise((resolve) => {
      this.db.close((err) => {
        if (err) util.err("数据库关闭失败", err);
        this.isConnected = false;
        resolve();
      });
    });
  }

  /**
   * 获取用户总数（含错误重试机制）
   * @returns {Promise<number>} 总记录数（0 表示无数据）
   */
  async getCount() {
    await this.connect(); // 自动等待连接

    return new Promise((resolve, reject) => {
      this.db.get(
        "SELECT COUNT(*) AS total FROM users", // SQLite 标准计数
        (err, row) => {
          if (err) {
            util.err("用户计数失败", err);
            // ✅ 重试机制（连接断开时重试一次）
            if (err.message.includes("not open")) {
              return this.connect().then(() =>
                this.db.get("SELECT COUNT(*) AS total FROM users", resolve)
              );
            }
            return reject(err);
          }
          resolve(row ? row.total : 0); // 处理表不存在时的 null（返回 0）
        }
      );
    });
  }

  async login(username, password) {
    await this.connect(); // 自动等待连接
    return new Promise((resolve, reject) => {
      const sql = "SELECT * FROM users WHERE username = ? AND password = ?";
      this.db.get(sql, [username, password], (err, result) => {
        err ? reject(err) : resolve(result);
      });
    });
  }
}

module.exports = UserDB;
